if exists (select * from dbo.sysobjects where id = 
object_id(N'[sp_GetRepliesByPostID]') and OBJECTPROPERTY(id, N'IsProcedure') 
= 1)
   drop procedure [sp_GetRepliesByPostID]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

-- setuser N'springofwat'
-- GO

CREATE PROCEDURE sp_GetRepliesByPostID
@PostID int,
@StartIndex int,
@NumberOfRecords int,
@Type  varchar(10),
@Status varchar (10),
@CategoryID int,
@OrderBy varchar(20),
@TotalRecords int output
AS
DECLARE @SelectSQL  VARCHAR(4000),
                 @WHERE       INT

SELECT @WHERE = 0
SELECT @SelectSQL = 'SELECT * FROM ForumPosts'

IF ISNULL(@PostID, 0) > 0
BEGIN
  SELECT @SelectSQL = @SelectSQL + ' WHERE ParentPostID = ' + CAST(@PostID 
AS VARCHAR(20))
  SET @WHERE = 1
END

IF ISNULL(@CategoryId, 0) > 0
IF @WHERE = 0
BEGIN
  SELECT @SelectSQL = @SelectSQL + ' WHERE CategoryId = ' + CAST(@CategoryId 
AS VARCHAR(20))
  SET @WHERE = 1
END ELSE
BEGIN
  SELECT @SelectSQL = @SelectSQL + ' AND CategoryId = ' + CAST(@CategoryId 
AS VARCHAR(20))
END

IF LEN(ISNULL(@Status, '')) > 0
IF @WHERE = 0
BEGIN
      SELECT @SelectSQL = @SelectSQL + ' WHERE Status = ''' + @Status + ''''
      SET @WHERE = 1
END ELSE
      SELECT @SelectSQL = @SelectSQL + ' AND Status =''' + @Status + ''''

IF LEN(ISNULL(@Type, '')) > 0
IF @WHERE = 0
BEGIN
      SELECT @SelectSQL = @SelectSQL + ' WHERE Type ="' + @Type + ''''
      SET @WHERE = 1
END ELSE
      SELECT @SelectSQL = @SelectSQL + ' AND Type = ''' + @Type + ''''



SELECT  @SelectSQL = @SelectSQL + ' Order By ' + @OrderBy

print   @SelectSQL

/* This is bad that we have to list all the column definition here */
/* I tried to use 'select into #tempposts from forumposts' and    */
/* 'alter table #tempposts alter column postid int (4)' but didn't  */
/* work.                                                                     
                */
/* The main idea is to add a new identity column rownum. That*/
/* is all because T-SQL does not provide rownum column        */
create table #tempposts(rownum int identity(1,1)
                       ,PostID  int
                       ,CategoryID int
                       ,ParentPostID int
                       ,Subject nvarchar(400)
                       ,Body ntext
                       ,CreateDate datetime
                       ,Status varchar(10)
                       ,Summary nvarchar(2000)
                       ,Type varchar(50)
                       ,UserID int
                       ,NumberOfVisits int
                       ,PartnerID int
                       ,OriginAuthor nvarchar(400)
                       ,URL varchar(1000)
                       ,OriginalDate datetime
                       ,LastModifiedBy int
                       ,LastModifiedDateTime datetime)

insert into #tempposts(PostID
                      ,CategoryID
                      ,ParentPostID
                      ,Subject
                      ,Body
                      ,CreateDate
                      ,Status
                      ,Summary
                      ,Type
                      ,UserID
                      ,NumberOfVisits
                      ,PartnerID
                      ,OriginAuthor
                      ,URL
                      ,OriginalDate
                      ,LastModifiedBy
                      ,LastModifiedDateTime)
                       EXEC(@SelectSQL)

select @TotalRecords = count(*) from #tempposts

IF ISNULL(@NumberOfRecords, 0) > 0
    select * from #tempposts left join Users on #tempposts.UserID = 
Users.UserID
		 where rownum >= @StartIndex  And rownum  < @StartIndex + @NumberOfRecords
ELSE
    select * from #tempposts left join Users on #tempposts.UserID = 
Users.UserID
                          where rownum >= @StartIndex

Drop table #tempposts
GO
setuser
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

/*
sp_GetRepliesByPostID 71, 1, 10, 'bbs', null, 40, 'categoryid', 0
*/

